package cn.com.checne.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import cn.com.checne.model.domain.LanguageInfo;
import cn.com.checne.model.domain.PerformanceCategory;
import cn.com.checne.model.domain.PerformanceInfo;
import cn.com.checne.model.dto.PerformanceQuery;
import cn.com.checne.util.TimeUtils;



@Repository
public class PerformanceInfoDao {
	protected static final Logger log = Logger.getLogger(PerformanceInfoDao.class);

	/**
	 * create
	 * @param cate
	 * @return
	 */
	public boolean create(PerformanceInfo Performance) {
		boolean result = false;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			conn.setAutoCommit(false);
			String createSql = "insert into tbl_Performance(lg_id_fk,performance_cate_id_fk,performance_title,performance_summary,performance_publishdate,performance_publishfrom,performance_author,performance_contenttype,performance_content," +
					"performance_meta_keyword,performance_meta_content,performance_isshowindex,performance_isshowlist,performance_istop,performance_firstimage,performance_order)" +
					" values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			PreparedStatement pstmt = conn.prepareStatement(createSql,Statement.RETURN_GENERATED_KEYS);
			int i = 0 ;
			pstmt.setInt(++i,Performance.getLanguageId());
			pstmt.setInt(++i,Performance.getCateId());
			pstmt.setString(++i,Performance.getTitle());
			pstmt.setString(++i,Performance.getSummary());
			pstmt.setTimestamp(++i, new java.sql.Timestamp(Performance.getPublishDate().getTime()));
			pstmt.setString(++i,Performance.getPublishFrom());
			pstmt.setString(++i,Performance.getAuthor());
			pstmt.setInt(++i,Performance.getContentType());
			pstmt.setString(++i,Performance.getContent());
			
			pstmt.setString(++i,Performance.getMetaKeyword());
			pstmt.setString(++i,Performance.getMetaContent());
			
			pstmt.setInt(++i,Performance.getIsShowIndex());
			pstmt.setInt(++i,Performance.getIsShowList());
			pstmt.setInt(++i,Performance.getIsTop());
			
			pstmt.setString(++i,Performance.getFirstImage());
			pstmt.setInt(++i,-1);
			pstmt.execute();
			
			ResultSet rsKey = pstmt.getGeneratedKeys();
			rsKey.next();
			int key = rsKey.getInt(1);
			
			String updateSql = "update tbl_performance set performance_order = ? where performance_id = ? " ;
			pstmt = conn.prepareStatement(updateSql);
			pstmt.setInt(1, key);
			pstmt.setInt(2, key);
			pstmt.executeUpdate();
			
			conn.commit();
			conn.close();
			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false ;
		}
		return result;
	}

	/**
	 * @param cate
	 * @return
	 */
	public boolean update(PerformanceInfo Performance) {
		boolean result = false;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String updateSql = "update tbl_Performance set " +
					"lg_id_fk = ?,performance_cate_id_fk = ?,performance_title = ?,performance_summary = ?,performance_publishdate =? ,performance_publishfrom = ?,performance_author =?,performance_contenttype=?,performance_content=?," +
					"performance_meta_keyword =?,performance_meta_content=?,performance_isshowindex=?,performance_isshowlist=?,performance_istop=?,performance_firstimage=? ,performance_order = ? where performance_id = ? ";
			PreparedStatement pstmt = conn.prepareStatement(updateSql);
			int i = 0 ;
			pstmt.setInt(++i,Performance.getLanguageId());
			pstmt.setInt(++i,Performance.getCateId());
			pstmt.setString(++i,Performance.getTitle());
			pstmt.setString(++i,Performance.getSummary());
			pstmt.setTimestamp(++i, new java.sql.Timestamp(Performance.getPublishDate().getTime()));
			pstmt.setString(++i,Performance.getPublishFrom());
			pstmt.setString(++i,Performance.getAuthor());
			pstmt.setInt(++i,Performance.getContentType());
			pstmt.setString(++i,Performance.getContent());
			
			pstmt.setString(++i,Performance.getMetaKeyword());
			pstmt.setString(++i,Performance.getMetaContent());
			
			pstmt.setInt(++i,Performance.getIsShowIndex());
			pstmt.setInt(++i,Performance.getIsShowList());
			pstmt.setInt(++i,Performance.getIsTop());
			
			pstmt.setString(++i,Performance.getFirstImage());
			
			pstmt.setInt(++i,Performance.getOrder());

			pstmt.setInt(++i, Performance.getPerformanceId());
			
			pstmt.executeUpdate();
			conn.close();
			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false ;
		}
		return result;
	}

	/**
	 * @param id
	 * @return
	 */
	public boolean delete(int id){
		boolean result = false;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String updateSql = "delete from tbl_Performance where performance_id = ? ";
			PreparedStatement pstmt = conn.prepareStatement(updateSql);
			int i = 0 ;
			pstmt.setInt(++i,id);
			log.info("[SQL]" + pstmt.toString());
			pstmt.executeUpdate();
			conn.close();
			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false ;
		}
		return result ;
	}
	/**
	 * 
	 * @param PerformanceId
	 * @return
	 */
	public PerformanceInfo getPerformanceById(int PerformanceId) {
		PerformanceInfo Performance = new PerformanceInfo();
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select * from tbl_Performance where performance_id = ? ";

			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, PerformanceId);
			log.info("[SQL]" + pstmt.toString());
			ResultSet rs = pstmt.executeQuery();
			List<PerformanceInfo> list = getResult(rs);
			Performance = (list != null && list.size() > 0 ) ? list.get(0) : Performance ;
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return Performance;
	}
	
	public PerformanceInfo getPerformanceBySQL(String sql) {
		PerformanceInfo Performance = null ;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			List<PerformanceInfo> list = getResult(rs);
			Performance = (list != null && list.size() > 0 ) ? list.get(0) : Performance ;
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return Performance;
	}
	
	public void executeSQL(String sql) {
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			Statement stmt = conn.createStatement();
			stmt.executeUpdate(sql);
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	/**
	 * 
	 * @param query
	 * @param start
	 * @param num
	 * @return
	 */
	public List<PerformanceInfo> getPerformanceListByCondition(PerformanceQuery query,int start,int num) {
		List<PerformanceInfo> list = new ArrayList<PerformanceInfo>();
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select * from tbl_Performance where 1=1 " ;
			if(query.getLanguageId() > 0){
				sql += " and lg_id_fk = ? " ;
			}
			if(query.getCateId() > 0){
				sql += " and performance_cate_id_fk = ? " ;
			}
			if(query.getIsShowIndex() > -1){
				sql += " and performance_isshowindex = ? " ;
			}
			if(query.getIsShowList() > -1){
				sql += " and performance_isshowlist = ? " ;
			}
			if(query.getIsTop() > -1){
				sql += " and performance_istop = ? " ;
			}
			if(StringUtils.isNotBlank(query.getFirstImage())){
				sql += " and performance_firstimage <> '' " ;
			}
			if(StringUtils.isNotBlank(query.getKw())){
				sql += " and (performance_title like ? or performance_meta_keyword like ? or performance_meta_content like ? or performance_content like ? )";
			}
			if(StringUtils.isNotBlank(query.getStartTime())){
				sql += " and performance_publishdate >= ? ";
			}
			if(StringUtils.isNotBlank(query.getEndTime())){
				sql += " and performance_publishdate <= ? ";
			}
			
			sql += "order by " ;
			if(query.getOrder() > -1){
				if(query.getOrder() == 0){
					sql += " performance_publishdate desc" ;
				}
				if(query.getOrder() == 1){
					sql += " performance_order asc" ;
				}
			}
			sql += "  limit ? ,?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			int i = 0 ;
			if(query.getLanguageId() > 0){
				pstmt.setInt(++i, query.getLanguageId());
			}
			if(query.getCateId() > 0){
				pstmt.setInt(++i, query.getCateId());
			}
			if(query.getIsShowIndex() > -1){
				pstmt.setInt(++i, query.getIsShowIndex());
			}
			if(query.getIsShowList() > -1){
				pstmt.setInt(++i, query.getIsShowList());
			}
			if(query.getIsTop() > -1){
				pstmt.setInt(++i, query.getIsTop());
			}
			if(StringUtils.isNotBlank(query.getKw())){
				pstmt.setString(++i, "%" + query.getKw() + "%");
				pstmt.setString(++i, "%" + query.getKw() + "%");
				pstmt.setString(++i, "%" + query.getKw() + "%");
				pstmt.setString(++i, "%" + query.getKw() + "%");
			}
			if(StringUtils.isNotBlank(query.getStartTime())){
				pstmt.setTimestamp(++i, new java.sql.Timestamp(TimeUtils.formatDate(query.getStartTime()).getTime()));
			}
			if(StringUtils.isNotBlank(query.getEndTime())){
				pstmt.setTimestamp(++i, new java.sql.Timestamp(TimeUtils.formatDate(query.getEndTime()).getTime()));
			}
			pstmt.setInt(++i, start);
			pstmt.setInt(++i, num);
			log.info("[SQL]" + pstmt.toString());
			ResultSet rs = pstmt.executeQuery();
			list.addAll(getResult(rs));
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * 
	 * @param query
	 * @return
	 */
	public int getPerformanceCountByCondition(PerformanceQuery query) {
		int total = 0 ;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select count(1) as total from tbl_Performance where 1=1 " ;
			if(query.getLanguageId() > 0){
				sql += " and lg_id_fk = ? " ;
			}
			if(query.getCateId() > 0){
				sql += " and performance_cate_id_fk = ? " ;
			}
			if(query.getIsShowIndex() > -1){
				sql += " and performance_isshowindex = ? " ;
			}
			if(query.getIsShowList() > -1){
				sql += " and performance_isshowlist = ? " ;
			}
			if(query.getIsTop() > -1){
				sql += " and performance_istop = ? " ;
			}
			if(StringUtils.isNotBlank(query.getFirstImage())){
				sql += " and performance_firstimage <> '' " ;
			}
			if(StringUtils.isNotBlank(query.getKw())){
				sql += " and (performance_title like ? or performance_meta_keyword like ? or performance_meta_content like ? or performance_content like ? )";
			}
			if(StringUtils.isNotBlank(query.getStartTime())){
				sql += " and performance_publishdate >= ? ";
			}
			if(StringUtils.isNotBlank(query.getEndTime())){
				sql += " and performance_publishdate <= ? ";
			}
			PreparedStatement pstmt = conn.prepareStatement(sql);
			int i = 0 ;
			if(query.getLanguageId() > 0){
				pstmt.setInt(++i, query.getLanguageId());
			}
			if(query.getCateId() > 0){
				pstmt.setInt(++i, query.getCateId());
			}
			if(query.getIsShowIndex() > -1){
				pstmt.setInt(++i, query.getIsShowIndex());
			}
			if(query.getIsShowList() > -1){
				pstmt.setInt(++i, query.getIsShowList());
			}
			if(query.getIsTop() > -1){
				pstmt.setInt(++i, query.getIsTop());
			}
			if(StringUtils.isNotBlank(query.getKw())){
				pstmt.setString(++i, "%" + query.getKw() + "%");
				pstmt.setString(++i, "%" + query.getKw() + "%");
				pstmt.setString(++i, "%" + query.getKw() + "%");
				pstmt.setString(++i, "%" + query.getKw() + "%");
			}
			if(StringUtils.isNotBlank(query.getStartTime())){
				pstmt.setTimestamp(++i, new java.sql.Timestamp(TimeUtils.formatDate(query.getStartTime()).getTime()));
			}
			if(StringUtils.isNotBlank(query.getEndTime())){
				pstmt.setTimestamp(++i, new java.sql.Timestamp(TimeUtils.formatDate(query.getEndTime()).getTime()));
			}
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()){
				total = rs.getInt("total");
			}
			rs.close();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return total;
	}
	
	
	public boolean setShowIndex(int flag,int...PerformanceIds){
		boolean result = false;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String updateSql = "update tbl_Performance set performance_isshowindex = ? where performance_id = ? ";
			PreparedStatement pstmt = conn.prepareStatement(updateSql);
			for(int PerformanceId : PerformanceIds){
				int i = 0 ;
				pstmt.setInt(++i,flag);
				pstmt.setInt(++i,PerformanceId);
				pstmt.addBatch();
			}
			pstmt.executeBatch();
			conn.close();
			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false ;
		}
		return result ;
	}
	
	public boolean setShowList(int flag,int...PerformanceIds){
		boolean result = false;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String updateSql = "update tbl_Performance set performance_isshowlist = ? where performance_id = ? ";
			PreparedStatement pstmt = conn.prepareStatement(updateSql);
			for(int PerformanceId : PerformanceIds){
				int i = 0 ;
				pstmt.setInt(++i,flag);
				pstmt.setInt(++i,PerformanceId);
				pstmt.addBatch();
			}
			pstmt.executeBatch();
			conn.close();
			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false ;
		}
		return result ;
	}
	
	public PerformanceInfo isPerformanceExist(int languageId ,int cateId){
		PerformanceInfo Performance = null ;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select * from tbl_Performance where lg_id_fk = ? and performance_cate_id_fk = ?   " ;
			PreparedStatement pstmt = conn.prepareStatement(sql);
			int i = 0 ;
			pstmt.setInt(++i, languageId);
			pstmt.setInt(++i, cateId);
			log.info("[SQL]" + pstmt.toString());
			ResultSet rs = pstmt.executeQuery();
			List<PerformanceInfo> list = getResult(rs);
			Performance = (list != null && list.size() > 0 ) ? list.get(0) : null;
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return Performance ;
	}
	/**
	 * get rseult from resultSet
	 * @param rs
	 * @return
	 */
	public List<PerformanceInfo> getResult(ResultSet rs) {
		List<PerformanceInfo> list = new ArrayList<PerformanceInfo>();
		try {
			PerformanceInfo Performance = null ;
			while(rs.next()) {
				Performance = new PerformanceInfo();
				Performance.setPerformanceId(rs.getInt("performance_id"));
				Performance.setTitle(rs.getString("performance_title"));
				Performance.setSummary(rs.getString("performance_summary"));
				Performance.setPublishDate(rs.getTimestamp("performance_publishdate"));
				Performance.setPublishFrom(rs.getString("performance_publishfrom"));
				Performance.setAuthor(rs.getString("performance_author"));
				Performance.setContentType(rs.getInt("performance_contenttype"));
				Performance.setContent(rs.getString("performance_content"));
				Performance.setMetaKeyword(rs.getString("performance_meta_keyword"));
				Performance.setMetaContent(rs.getString("performance_meta_content"));
				Performance.setIsShowIndex(rs.getInt("performance_isshowindex"));
				Performance.setIsShowList(rs.getInt("performance_isshowlist"));
				Performance.setIsTop(rs.getInt("performance_istop"));
				Performance.setFirstImage(rs.getString("performance_firstimage"));
				Performance.setOrder(rs.getInt("performance_order"));

				Performance.setLanguageId(rs.getInt("lg_id_fk"));
				LanguageInfo lg = new LanguageInfoDao().getLanguageInfoById(Performance.getLanguageId());
				Performance.setLanguageInfo(lg);
				
				Performance.setCateId(rs.getInt("performance_cate_id_fk"));
				PerformanceCategory cate = new PerformanceCategoryDao().getPerformanceCategory(Performance.getCateId());
				Performance.setPerformanceCategory(cate);
				
				list.add(Performance);
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	public void sort(int performanceId,int orderId,String flag){
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			conn.setAutoCommit(false);
			String selectSQL = "" ,updateSQL = "" ;
			// 上移 需要把order-1
			if(StringUtils.equalsIgnoreCase(flag, "u")){
				//先找到小于当前order的对象
				selectSQL = "select * from tbl_performance where performance_order < " + orderId + " order by performance_order desc limit  0,1" ;
			}
			// 下移 需要把order+1
			else{
				selectSQL = "select * from tbl_performance where performance_order > " + orderId + " order by performance_order asc limit 0,1" ;
			}
			
			PerformanceInfo p = getPerformanceBySQL(selectSQL);
			if(p != null){
				int destId = p.getPerformanceId();
				int destOrderId = p.getOrder();
				//更新自身的orderId
				updateSQL = "update tbl_performance set performance_order = " + destOrderId + " where performance_id = " + performanceId ;
				executeSQL(updateSQL);
				
				//更新获取到的替换位置的orderId
				updateSQL = "update tbl_performance set performance_order = " + orderId + " where performance_id = " + destId ;
				executeSQL(updateSQL);
				
			}
			conn.commit();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public void setPerformanceOrder(String [] orderIdArray,String [] noIdArray){
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			conn.setAutoCommit(false);
			String updateSQL = " update tbl_performance set performance_order = ? where performance_id = ? " ;
			int batchLen = orderIdArray.length ;
			PreparedStatement pstmt = conn.prepareStatement(updateSQL);
			for(int i = 0 ; i < batchLen ; i ++){
				pstmt.setInt(1, Integer.valueOf(orderIdArray[i]));
				pstmt.setInt(2, Integer.valueOf(noIdArray[i]));
				pstmt.addBatch();
			}
			int result [] = pstmt.executeBatch();
			conn.commit();
			conn.close();
			log.info("[checne][setPerformanceOrder] result = " + result.length);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}